﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using vHCS.HR.Entities;
using vHCS.HR.Business;
using Core.Controls.Message;
using System.Data;
using Telerik.Web.UI;
using Core.Message.Entities;
using Core.ExceptionHandling;
using vHCS.LS.Business;
using Core.Common.Session;
using Aspose.Cells;
using System.Collections;
using System.IO;
using System.Configuration;
using vHCS.K2App.Shared;
using K2CoreLib.DataObject;
namespace vHCS.K2App.MdlK2
{
    public partial class TER_ExitFormHis : Core.NTier.UserInterface.BaseFormNotPer
    {
        #region Const
        private const string _EMP_ID = "EmpID";
        private const string _List_Page = "~/MdlHR/TER_TerminalList.aspx";
        private List<ExitTypeApproveDto> listApprove = null;
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                // Check user incharge to view history
                string userAccess = SessionManager.UserDomain;
                int Id = Convert.ToInt32(Request.QueryString["ProcessID"].ToString());
                HistoryComment his = new HistoryComment();
                if (!his.CheckUserInCharge(userAccess, Id, 5)) // If user not incharge
                {
                    Response.Redirect("../UnAuthorize.htm");
                }

                K2CoreLib.DataProvider.ExitChecklistAction exitAction = new K2CoreLib.DataProvider.ExitChecklistAction();
                listApprove = exitAction.GetUserApprover(Convert.ToInt64(EmpHeader.EmployeeId).ToString());

                if (!IsPostBack)
                {
                    HR_WorkingRecordBL bl = new HR_WorkingRecordBL();
                    HR_WorkingRecordCollection cl = new HR_WorkingRecordCollection();
                    string empid = Request.QueryString[_EMP_ID] == null ? "" : Request.QueryString[_EMP_ID].ToString();
                    if (!string.IsNullOrEmpty(empid))
                    {
                        this.EmpHeader.LoadEmpInfoByEmpId(Convert.ToInt64(empid));
                        this.LoadInfo(Convert.ToInt64(EmpHeader.EmployeeId));
                        bl = cl.SelectLastWRByEmpID(EmpHeader.EmployeeId);
                        if (bl.DTO != null)
                            lblITOther.Text = lblITOther.Text.Replace("$Seat$", bl.Seat);
                    }

                    #region Lấy thông tin người request có quan tâm đến thông tin trợ cấp thất nghiệp hay không
                    TER_ExitFormCollection cl_exitform = new TER_ExitFormCollection();
                    if (cl_exitform.ReturnAllowanceInf(Convert.ToInt64(string.IsNullOrEmpty(empid) ? "0" : empid)))
                    {
                        radBenifit_Y.Checked = true;
                        radBenifit_N.Checked = false;
                    }
                    else
                    {
                        radBenifit_Y.Checked = false;
                        radBenifit_N.Checked = true;
                    }

                    if (cl_exitform.ReturnConsultInf(Convert.ToInt64(string.IsNullOrEmpty(empid) ? "0" : empid)))
                    {
                        radConsult_Y.Checked = true;
                        radConsult_N.Checked = false;
                    }
                    else
                    {
                        radConsult_Y.Checked = false;
                        radConsult_N.Checked = true;
                    }

                    #endregion

                    //ClientScript.RegisterStartupScript(Page.GetType(), "Benifit", "CheckBenifit();", true);
                    ClientScript.RegisterStartupScript(Page.GetType(), "Consult", "CheckConsult();", true);

                    #region Lấy thông tin HRBP
                    
                    K2Collection col_k2 = new K2Collection();
                    HR_EmpCVCollection cl_empcv = new HR_EmpCVCollection();
                    string HRBP = col_k2.GetUserOfHRP(EmpHeader.EmployeeId);
                    string NameHRBP = cl_empcv.GetDisplayNameByUser(HRBP);
                    long EmpID_HRBP = cl_empcv.GetEmpIdByUsername(HRBP);
                    string ext = "";
                    if (EmpID_HRBP > 0)
                    {
                        bl = cl.SelectLastWRByEmpID_ForFormWR1(EmpID_HRBP);
                        if (bl.DTO != null)
                        {
                            ext = bl.ExtNo;
                        }
                    }

                    lblConsult.Text = lblConsult.Text.Replace("$HRBP$", NameHRBP);
                    lblConsult.Text = lblConsult.Text.Replace("$ext$",string.IsNullOrEmpty(ext)?"":ext.Trim());
                    #endregion
                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
        }
        /// <summary>
        /// xử lý sự kiện khi thay đổi nhân viên khác
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void EmpHeader_OnUserChanged(object sender, EventArgs e)
        {
            try
            {
                if (this.EmpHeader.EmployeeId > 0)
                {
                    hdfEmpId.Value = this.EmpHeader.EmployeeId.ToString();
                    this.LoadInfo(this.EmpHeader.EmployeeId);
                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
        }
        /// <summary>
        /// Load thông tin eixt form tương ứng với emp id
        /// </summary>
        /// <param name="empId"></param>id nhân viên theo kiểu long
        protected void LoadInfo(long empId)
        {
            try
            {
                K2Collection colK2 = new K2Collection();
                TER_ExitFormCollection cl = new TER_ExitFormCollection();
                DataTable dat = new DataTable();
                dat = cl.SelectByEmpId(empId);
                HR_WorkingRecordBL bl_wr = new HR_WorkingRecordBL();
                HR_WorkingRecordCollection cl_wr = new HR_WorkingRecordCollection();
                HIE_EmpInHierarchyCollection cl_emphie = new HIE_EmpInHierarchyCollection();
                HIE_HierarchyBL bl_hie = new HIE_HierarchyBL();

                #region Lấy thông tin Dept Head có thuộc FA Other
                //Lấy thông tin WR hiện tại của nhân viên
                bl_wr = cl_wr.SelectLastWRByEmpID(empId);

                long hierarchyid = 0;
                if (bl_wr.DTO != null)
                    hierarchyid = bl_wr.HierarchyId;

                //

                //Lấy thông tin Dept Head của nhân viên
                long DeptID = cl_emphie.SelectDeptHie(hierarchyid);

                bool CheckFAOther = colK2.CheckDeptFA(DeptID.ToString());
                hdfCheckFAOther.Value = CheckFAOther.ToString();
                //

                #endregion

                if (dat.Rows.Count > 0)
                {
                    this.txtStartDate.Text = DateTime.Parse(dat.Rows[0]["JoinDate"].ToString().Trim()).ToShortDateString();
                    if (dat.Rows[0]["LastWorkingDate"].ToString().Trim() != "")
                    {
                        this.txtEndDate.Text = DateTime.Parse(dat.Rows[0]["LastWorkingDate"].ToString().Trim()).ToShortDateString();
                    }
                    dtsExitList.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsExitList.CacheClear();
                    dtsFAExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsFAExit.CacheClear();
                    dtsOtherHRExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsOtherHRExit.CacheClear();
                    dtsAFExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsAFExit.CacheClear();
                    dtsHRExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsHRExit.CacheClear();
                    dtsITExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsITExit.CacheClear();
                    dtsAFExitOther.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsAFExitOther.CacheClear();
                    dtsITOther.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsITOther.CacheClear();
                    dtsFAExitOther.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsFAExitOther.CacheClear();
                    dtsHRTraining.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsHRTraining.CacheClear();

                    dtgIT.DataBind();
                    dtgExitList.DataBind();
                    dtgHROtherExit.DataBind();
                    dtgFAExit.DataBind();
                    dtgAFExit.DataBind();
                    dtgHRExit.DataBind();
                    dtgAFExitOther.DataBind();
                    dtgITOther.DataBind();
                    dtgFAExitOther.DataBind();
                    dtgHRTrainingExit.DataBind();

                    #region Ẩn các lưới không có dữ liệu
                    if (dtgIT.Items.Count <= 0)
                        pnlIT.Visible = false;
                    if (dtgExitList.Items.Count <= 0)
                        pnlExitList.Visible = false;
                    if (dtgHROtherExit.Items.Count <= 0)
                        pnlHROtherExit.Visible = false;
                    if (dtgFAExit.Items.Count <= 0)
                        pnlFAExit.Visible = false;
                    if (dtgAFExit.Items.Count <= 0)
                        pnlAFExit.Visible = false;
                    if (dtgHRExit.Items.Count <= 0)
                        pnlHRExit.Visible = false;
                    if (dtgAFExitOther.Items.Count <= 0)
                        pnlAFExitOther.Visible = false;
                    if (dtgITOther.Items.Count <= 0)
                        pnlITOther.Visible = false;
                    if (dtgFAExitOther.Items.Count <= 0 || CheckFAOther == false)
                        pnlFAExitOther.Visible = false;
                    if (dtgHRTrainingExit.Items.Count <= 0)
                        pnlHRTrainingExit.Visible = false;
                    #endregion

                    ReplaceExtNoAndUser();

                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
        }

        protected string GetApprover(List<ExitTypeApproveDto> list, int ExitType, bool property)
        {
            string result = string.Empty;
            result = listApprove.Where(n => n.ExitTypeID == ExitType && n.Property == property).Single().Approver;

            return result;
        }
        protected string GetExtNo(List<ExitTypeApproveDto> list, string approver)
        {
            string result = string.Empty;
            if (list.Where(n => n.Approver == approver).Count() > 0)
            {
                result = list.Where(n => n.Approver == approver).First().ExtNo;
            }

            if (string.IsNullOrEmpty(result) || result == "") return "";

            return " (Ext." + result + ")";
        }

        protected void ReplaceExtNoAndUser()
        {
            var approver = GetApprover(listApprove, 2, false);
            lblAFExit.Text = lblAFExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, 11, true);
            lblAFExitOther.Text = lblAFExitOther.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.FA, true);
            lblFAExit.Text = lblFAExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.FAOther, true);
            lblFAExitOther.Text = lblFAExitOther.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.IT, false);
            lblIT.Text = lblIT.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.ITOther, true);
            lblITOther.Text = lblITOther.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.HR, false);
            lblHRExit.Text = lblHRExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.HROther, true);
            lblHROtherExit.Text = lblHROtherExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.HRTraining, true);
            lblHRTrainingExit.Text = lblHRTrainingExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));
        }

        #region Export data follow template
        public string ExportExcel(string sPathFile, DataTable dtData, DataTable dtData_AF, DataTable dtData_IT, DataTable dtData_FA, DataTable dtData_HR, DataTable dtData_Other, DataTable dtReplace)
        {
            try
            {
                Workbook wb = new Workbook();
                wb.Open(sPathFile);

                #region Cau hinh tren worksheet2
                Worksheet ws_config = wb.Worksheets[1];
                //--Khai bao tham so cau hinh
                int iMax_Colum = Convert.ToInt16(ws_config.Cells["H4"].Value);
                int iRow_Group1 = Convert.ToInt16(ws_config.Cells["B4"].Value) - 1;
                int iRow_Group2 = Convert.ToInt16(ws_config.Cells["B5"].Value) - 1;
                int iRow_Detail = Convert.ToInt16(ws_config.Cells["B6"].Value) - 1;
                //begin
                int iRow_Detail_AF = Convert.ToInt16(ws_config.Cells["B9"].Value) - 1;
                int iRow_Detail_IT = Convert.ToInt16(ws_config.Cells["B10"].Value) - 1;
                int iRow_Detail_FA = Convert.ToInt16(ws_config.Cells["B11"].Value) - 1;
                int iRow_Detail_HR = Convert.ToInt16(ws_config.Cells["B12"].Value) - 1;
                int iRow_Detail_Other = Convert.ToInt16(ws_config.Cells["B13"].Value) - 1;

                //end
                int iRow_End = Convert.ToInt16(ws_config.Cells["B7"].Value) - 1;

                string sNo = ws_config.Cells["B8"].Value.ToString();
                string sGroup1Active = ws_config.Cells["D4"].Value.ToString();
                string sGroup2Active = ws_config.Cells["D5"].Value.ToString();
                string sRowEndActive = ws_config.Cells["D7"].Value.ToString();

                string sGroup1Column = ws_config.Cells["E4"].Value == null ? "" : ws_config.Cells["E4"].Value.ToString();
                string sGroup2Column = ws_config.Cells["E5"].Value == null ? "" : ws_config.Cells["E5"].Value.ToString();

                string sVersionExcel = ws_config.Cells["H5"].Value == null ? "2003" : ws_config.Cells["H5"].Value.ToString();
                string sFileNameExcel = ws_config.Cells["H6"].Value == null ? "" : ws_config.Cells["H6"].Value.ToString();
                string sPassword = ws_config.Cells["H7"].Value == null ? "" : ws_config.Cells["H7"].Value.ToString();
                #endregion

                Worksheet ws = wb.Worksheets[0];

                #region Lay vi tri cac tieu de
                //databind phòng Manager
                ArrayList arr = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol = new ArrayList();
                ArrayList arrIndexFormular = new ArrayList();
                int IndexRow = 0;
                bool bIsSTT = false; //false: khong co Cot STT; //true: co Cot STT

                for (int i = 0; i < iMax_Colum; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr.Add(sSearch);
                            arrIndexCol.Add(cTmp.Column);
                            IndexRow = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT = true;
                }
                #endregion
                #region lấy vị trí tiêu đề :AF
                //begin
                ArrayList arr_add = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_add = new ArrayList();
                ArrayList arrIndexFormular_add = new ArrayList();
                int IndexRow_add = 0;
                bool bIsSTT_add = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < iMax_Colum; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_AF, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_add.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_add.Add(sSearch);
                            arrIndexCol_add.Add(cTmp.Column);
                            IndexRow_add = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_add = true;
                }
                //end
                #endregion
                #region Lấy vị trí tiêu đề :IT
                //begin
                ArrayList arr_IT = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_IT = new ArrayList();
                ArrayList arrIndexFormular_IT = new ArrayList();
                int IndexRow_IT = 0;
                bool bIsSTT_IT = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < iMax_Colum; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_IT, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_IT.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_IT.Add(sSearch);
                            arrIndexCol_IT.Add(cTmp.Column);
                            IndexRow_IT = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_IT = true;
                }
                //end
                #endregion
                #region lấy vị trí tiêu đề :FA
                //databind phòng Manager
                ArrayList arr_FA = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_FA = new ArrayList();
                ArrayList arrIndexFormular_FA = new ArrayList();
                int IndexRow_FA = 0;
                bool bIsSTT_FA = false; //false: khong co Cot STT; //true: co Cot STT
                for (int i = 0; i < iMax_Colum; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_FA, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_FA.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_FA.Add(sSearch);
                            arrIndexCol_FA.Add(cTmp.Column);
                            IndexRow_FA = cTmp.Row;
                        }
                    }
                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_FA = true;
                }
                #endregion
                #region Lấy vị trí tiêu đề :HR
                //begin
                ArrayList arr_HR = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_HR = new ArrayList();
                ArrayList arrIndexFormular_HR = new ArrayList();
                int IndexRow_HR = 0;
                bool bIsSTT_HR = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < iMax_Colum; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_HR, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_HR.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_HR.Add(sSearch);
                            arrIndexCol_HR.Add(cTmp.Column);
                            IndexRow_HR = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_HR = true;
                }
                //end
                #endregion
                #region lấy vị trí tiêu đề :Other
                //databind phòng Manager
                ArrayList arr_Other = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_Other = new ArrayList();
                ArrayList arrIndexFormular_Other = new ArrayList();
                int IndexRow_Other = 0;
                bool bIsSTT_Other = false; //false: khong co Cot STT; //true: co Cot STT
                for (int i = 0; i < iMax_Colum; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_Other, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_Other.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_Other.Add(sSearch);
                            arrIndexCol_Other.Add(cTmp.Column);
                            IndexRow_Other = cTmp.Row;
                        }
                    }
                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_Other = true;
                }
                #endregion
                if (dtData != null)
                {
                    if (dtData.Rows.Count > 0)
                    {
                        #region insert cac detail
                        for (int i = 0; i < arr.Count; i++)
                        {
                            if (i == 0)
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(dtData, false, IndexRow, Convert.ToInt16(arrIndexCol[i]), arr[i].ToString(), true);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow, Convert.ToInt16(arrIndexCol[i])].PutValue(null);
                                }
                            }
                            else
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(dtData, false, IndexRow, Convert.ToInt16(arrIndexCol[i]), arr[i].ToString(), false);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow, Convert.ToInt16(arrIndexCol[i])].PutValue(null);
                                }
                            }
                        }

                        //--Sao chep cong thuc

                        for (int i = 0; i < arrIndexFormular.Count; i++)
                        {
                            Cell sTmp = ws.Cells[iRow_Detail, Convert.ToInt16(arrIndexFormular[i])];

                            for (int j = iRow_Detail + 1; j < iRow_Detail + dtData.Rows.Count; j++)
                            {
                                ws.Cells[j, Convert.ToInt16(arrIndexFormular[i])].Copy(sTmp);
                            }
                        }
                        //--End
                        ws.Cells.DeleteRow(IndexRow + dtData.Rows.Count);
                        #endregion
                    }
                    else
                        ws.Cells.DeleteRow(iRow_Detail);
                }
                int rowAF = 0;
                if (dtData_AF.Rows.Count > 0)
                {
                    rowAF = this.ExportDMProperty(iRow_Detail_AF, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                }
                else
                {
                    if (dtData.Rows.Count <= 0)
                    {
                        rowAF = this.ExportDMProperty(iRow_Detail_AF, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                    }
                    else
                        rowAF = this.ExportDMProperty(iRow_Detail_AF + 1, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                }
                int rowIT = 0;
                if (dtData_IT.Rows.Count > 0)
                {
                    rowIT = this.ExportDMProperty(iRow_Detail_IT, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                }
                else
                {
                    if (rowAF <= 0 && dtData.Rows.Count <= 0)
                    {
                        rowIT = this.ExportDMProperty(iRow_Detail_IT - 1, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                    }
                    else if ((rowAF <= 0 && dtData.Rows.Count > 0) || rowAF > 0 && dtData.Rows.Count <= 0)
                    {
                        rowIT = this.ExportDMProperty(iRow_Detail_IT, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                    }
                    else
                        rowIT = this.ExportDMProperty(iRow_Detail_IT + 1, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                }
                int rowFA = 0;
                if (dtData_FA.Rows.Count > 0)
                {
                    rowFA = this.ExportDMProperty(iRow_Detail_FA, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                }
                else
                {
                    if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0)
                    {
                        rowFA = this.ExportDMProperty(iRow_Detail_FA - 2, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    }
                    else if ((rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0))
                    {
                        rowFA = this.ExportDMProperty(iRow_Detail_FA - 1, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    }
                    else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0))
                    {
                        rowFA = this.ExportDMProperty(iRow_Detail_FA, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    }
                    else
                        rowFA = this.ExportDMProperty(iRow_Detail_FA + 1, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);


                }
                int rowHR = 0;
                if (dtData_HR.Rows.Count > 0)
                {
                    rowHR = this.ExportDMProperty(iRow_Detail_HR, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                }
                else
                {

                    if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0)
                    {
                        rowHR = this.ExportDMProperty(iRow_Detail_HR - 3, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    }
                    else if ((rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0))
                    {
                        rowHR = this.ExportDMProperty(iRow_Detail_HR - 2, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    }
                    else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0))
                    {
                        rowHR = this.ExportDMProperty(iRow_Detail_HR - 1, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    }
                    else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0))
                        rowHR = this.ExportDMProperty(iRow_Detail_HR, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    else
                        rowHR = this.ExportDMProperty(iRow_Detail_HR + 1, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);

                }
                int rowOther = 0;
                if (dtData_Other.Rows.Count > 0)
                {
                    rowOther = this.ExportDMProperty(iRow_Detail_Other, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                }
                else
                {
                    if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0)
                    {
                        rowOther = this.ExportDMProperty(iRow_Detail_Other - 4, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    }
                    else if ((rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR > 0))
                    {
                        rowOther = this.ExportDMProperty(iRow_Detail_Other - 3, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    }
                    else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0 && rowHR > 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0 && rowHR > 0))
                    {
                        rowOther = this.ExportDMProperty(iRow_Detail_Other - 2, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    }
                    else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0))
                        rowOther = this.ExportDMProperty(iRow_Detail_Other - 1, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0 && rowHR > 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0 && rowHR > 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0 && rowHR > 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0 && rowHR > 0))
                        rowOther = this.ExportDMProperty(iRow_Detail_Other, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    else
                        rowOther = this.ExportDMProperty(iRow_Detail_Other + 1, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);

                }
                #region replace parametter
                ArrayList arrReplace = new ArrayList();
                if (dtReplace != null)
                {
                    for (int i = 0; i < dtReplace.Columns.Count; i++)
                    {
                        string sColumnName = dtReplace.Columns[i].ToString();
                        object sColumnValue = dtReplace.Rows[0][sColumnName];

                        Cell c = ws.Cells.FindString("$" + sColumnName + "$", null);
                        if (c == null)
                            ws.Replace("$" + sColumnName + "$", sColumnValue.ToString());
                        else
                            c.PutValue(sColumnValue);
                    }
                }
                #endregion

                #region Password
                if (sPassword.Trim() != "")
                    ws.Protect(ProtectionType.All, sPassword, null);
                #endregion

                ws.Cells.DeleteRow(iRow_Group1);
                ws.Cells.DeleteRow(iRow_Group1);

                wb.Worksheets.RemoveAt(1);
                // wb.Worksheets.RemoveAt(1);


                MemoryStream Stream = new MemoryStream();
                FileFormatType Format = new Aspose.Cells.FileFormatType();

                if (sVersionExcel == "2007")
                {
                    Format = Aspose.Cells.FileFormatType.Excel2007Xlsx;
                    sFileNameExcel += ".xlsx";
                }

                else
                {
                    Format = Aspose.Cells.FileFormatType.Excel2003;
                    sFileNameExcel += ".xls";
                }
                wb.Save(Stream, Format);
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + sFileNameExcel);
                HttpContext.Current.Response.BinaryWrite(Stream.ToArray());
                HttpContext.Current.Response.End();

                return "";
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
            return "";
        }
        #region xuất danh mục đã tồn tại
        public int ExportDMProperty(int rowDetail, int rowBefore, DataTable datAF, int maxColumn, Worksheet ws, ArrayList arr_add
                , ArrayList arrIndexCol_add
                , ArrayList arrIndexFormular_add
                , int IndexRow_add)
        {
            try
            {

                //begin
                if (datAF != null)
                {

                    if (datAF.Rows.Count > 0)
                    {
                        #region insert cac detail
                        for (int i = 0; i < arr_add.Count; i++)
                        {
                            if (i == 0)
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), true);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                            else
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), false);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                        }

                        //--Sao chep cong thuc

                        for (int i = 0; i < arrIndexFormular_add.Count; i++)
                        {
                            Cell sTmp = ws.Cells[rowDetail, Convert.ToInt16(arrIndexFormular_add[i])];

                            for (int j = rowDetail + 1; j < rowDetail; j++)
                            {
                                ws.Cells[j, Convert.ToInt16(arrIndexFormular_add[i])].Copy(sTmp);
                            }
                        }
                        //--End

                        ws.Cells.DeleteRow(IndexRow_add + rowBefore + datAF.Rows.Count - 1);
                        #endregion

                    }
                    else
                        ws.Cells.DeleteRow(rowDetail);

                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
            return datAF.Rows.Count;
        }
        #endregion
        #region xuất danh mục chưa tồn tại
        public int ExportNotDMProperty(int rowDetail, int rowBefore, DataTable datAF, int maxColumn, Worksheet ws, ArrayList arr_add
                , ArrayList arrIndexCol_add
                , ArrayList arrIndexFormular_add
                , int IndexRow_add)
        {
            try
            {

                //begin
                if (datAF != null)
                {

                    if (datAF.Rows.Count > 0)
                    {
                        #region insert cac detail
                        for (int i = 0; i < arr_add.Count; i++)
                        {
                            if (i == 0)
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), true);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                            else
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), false);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                        }

                        //--Sao chep cong thuc

                        for (int i = 0; i < arrIndexFormular_add.Count; i++)
                        {
                            Cell sTmp = ws.Cells[rowDetail, Convert.ToInt16(arrIndexFormular_add[i])];

                            for (int j = rowDetail + 1; j < rowDetail; j++)
                            {
                                ws.Cells[j, Convert.ToInt16(arrIndexFormular_add[i])].Copy(sTmp);
                            }
                        }
                        //--End

                        ws.Cells.DeleteRow(IndexRow_add + rowBefore + datAF.Rows.Count - 1);
                        #endregion

                    }
                    else
                        ws.Cells.DeleteRow(rowDetail + 1);

                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
            return datAF.Rows.Count;
        }
        #endregion
        #endregion
    }
}